
package sg.edu.nus.iss.cats.dataaccess.mysql;

import sg.edu.nus.iss.cats.dataaccess.*;
import sg.edu.nus.iss.cats.model.*;

import java.util.*;
import java.sql.*;


public class UserDAOImpl implements UserDAO {

    RoleManager roleManager = new RoleManager ();

    public User findUser (String userId) throws DAOException{
        if (userId == null) {
            throw new DAOException ("Could not find User: specified id is null");
        }

        /** WORKSHOP W09_DATABASE
          * Complete the findUser method - find user depending on the user_id
          * 1) Write the correct SQL SELECT statement
          * 2) Pass it to the retrieveUsers() method
          * 3) Return the first User object in the array,
          *    or null if the array is zero-length
        *********/
        String sql = "SELECT * FROM " + DBConstants.usersTableName + " WHERE " + DBConstants.u_userId + "='" + userId + "'";
        User[] results = retrieveUsers (sql);
        if (results.length == 0) {
            return null;
        }
        return results[0];

    }

    public User[] findAllUsers () throws DAOException{
    
        /** WORKSHOP W09_DATABASE
        * Complete the findAllUsers method - Find all users
        * 1) Write the correct SQL SELECT statement
        * 2) Pass it to the retrieveUsers() method
        * 3) Return the resulting array
        *********/
        String sql = "SELECT * FROM " + DBConstants.usersTableName;
        User[] results = retrieveUsers (sql);
        return results;
    }

    public void insertUser (User user) throws DAOException {
        throw new DAOException ("Not yet implemented!");
    }

    public void updateUser (User user) throws DAOUpdateException {
        String userId = user.getUserId();
        if (userId == null) {
            throw new DAOUpdateException ("Could not find User: specified id is null");
        }
        String sql = "SELECT * FROM " + DBConstants.usersTableName + " WHERE " + DBConstants.u_userId + "='" + userId + "'";

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DBConstants.dbUrl,
                DBConstants.dbUsername,
                DBConstants.dbPassword);
            System.out.println("Created Connection");
            Statement statement = conn.createStatement(
                ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

            System.out.println("Executing query: " + sql);
            ResultSet rs = statement.executeQuery(sql);
            System.out.println("Executed query");

            rs.beforeFirst();
            do {
                rs.next();
                rs.updateString (DBConstants.u_name, user.getName());
                rs.updateString (DBConstants.u_password, user.getPassword());
                rs.updateString (DBConstants.u_employeeId, user.getEmployeeId());
                rs.updateRow ();
            } while (!rs.isLast());
            rs.close();
            statement.close ();
            System.out.println("Modified objects");

            // Update the user's roles too

            UserRoleDAOImpl userRoleDAO = new UserRoleDAOImpl ();
            userRoleDAO.updateRolesForUser (userId, user.getRoles());

        } catch (Exception e) {

            // If there is an error, we throw an exception.
            String error = "Error modifying object. Nested Exception is: " + e;
            System.out.println (error);
            throw new DAOUpdateException (error);

        } finally {
            try {
                System.out.println("Closing connection");
                conn.close();
            } catch (Exception e) {}
        }
    }

    public void deleteUser (User user) throws DAOException {
        throw new DAOException ("Not yet implemented!");
    }

    public Role[] findAllRoles () throws DAOException {
        Role[] result = roleManager.findAllRoles ();
        return result;
    }

    public Role findRole (String roleId) throws DAOException {
        return roleManager.findRole (roleId);
    }


    /**
     * This method accepts a SQL query and executs it.
     * It assumes the resulting ResultSet is from the USERS table,
     * and creates an array of User objects, one per returned row.
     * If there are no results, a zero-length array is returned.
     */
    private User[] retrieveUsers (String sql) throws DAOException {
        Connection conn = null;
        Vector v = new Vector();
        try {
            conn = DriverManager.getConnection(DBConstants.dbUrl,
                DBConstants.dbUsername,
                DBConstants.dbPassword);
            System.out.println("Created Connection");


             
            /** WORKSHOP W09_DATABASE
              * Create a Statement and execute the query
              *********/                         
                          
              
            Statement statement = conn.createStatement();
            System.out.println("Executing query: " + sql);

            ResultSet rs = statement.executeQuery(sql);
            System.out.println("Executed query");

            // Go through the results- there must be at the most one.
            while (rs.next()) {
                System.out.println("Processing row");
                
                /** WORKSHOP W09_DATABASE
                  * For each row,
                  * 1) create a User object
                  * 2) populate it with the ResultSet fields
                  *    Refer to your Database documentation for the field names
                  * 3) Add the resulting object to the Vector v
                *********/
                User obj = new User ();
                obj.setUserId     (rs.getString(DBConstants.u_userId));
                obj.setName       (rs.getString(DBConstants.u_name));
                obj.setPassword   (rs.getString(DBConstants.u_password));
                obj.setEmployeeId (rs.getString(DBConstants.u_employeeId));
                v.add (obj);
            }

            /** WORKSHOP W09_DATABASE
             * The query is executed: close ResultSet and query as appropriate.
            *********/
            rs.close();
            statement.close ();
            System.out.println("Retrieved users- number found: " + v.size());

        } catch (Exception e) {

            // If there is an error, we throw an exception.
            String error = "Error retrieving users. Nested Exception is: " + e;
            System.out.println (error);
            throw new DAOException (error);

        } finally {
            try {
                System.out.println("Closing connection");
                conn.close();
            } catch (Exception e) {}
        }

        // Go through the resulting array and, for each user, retrieve the roles
        // and add them to the User object

        UserRoleDAOImpl userRoleDAO = new UserRoleDAOImpl ();
        Iterator i = v.iterator ();
        while (i.hasNext()) {
            User user = (User) i.next ();
            
            /** WORKSHOP W09_DATABASE
              * 1) Get the user ID
              * 2) Pass it to the userRoleDAO object to get the list of roles
              *    (inspect userRoleDAO for details)
              * 3) Set the User's Roles with the array received
            *********/
            String userId = user.getUserId();
            Role[] userRoles = userRoleDAO.findRolesForUser(userId);
            user.setRoles (userRoles);
        }

        // Convert the Vector to an array of Users

        User[] result = new User [v.size()];
        result = (User[]) v.toArray (result);
        return (result);
    }
    
    public User[] findAllManagers () throws DAOException{            
    
            String sql = "SELECT u." + DBConstants.u_userId + ",u." +
                           DBConstants.u_name + ",u." + DBConstants.u_password +
                           ",u." + DBConstants.u_employeeId + " FROM " + 
                          DBConstants.usersTableName + " u," + 
                          DBConstants.userRolesTableName + " ur WHERE ur." +
                          DBConstants.ur_roleId + "='manager' and ur." + 
                          DBConstants.ur_userId + "= u." + DBConstants.u_userId;
            User[] results = retrieveUsers (sql);
            if (results.length == 0) {
                return null;
            }
            return results;
    }

}